using Dapper;
using InventoryIssue.API.Read.Application.Command.Allocate;
using InventoryIssue.Dtos;
using InventoryIssue.ErrorCode;
using InventoryIssue.Infrastructrue.Implement;
using MediatR;
using System.Text;

namespace InventoryIssue.API.Read.Application.Handler.Allocate
{
    public class GetOperationRecordHandler : IRequestHandler<GetOperationRecordCommand, APIPaging<OperationRecordDto>>
    {
        private readonly DatabaseContext db;

        public GetOperationRecordHandler(DatabaseContext db)
        {
            this.db = db;
        }

        public async Task<APIPaging<OperationRecordDto>> Handle(GetOperationRecordCommand request, CancellationToken cancellationToken)
        {
            APIPaging<OperationRecordDto> result = new APIPaging<OperationRecordDto>();
            StringBuilder sql = new StringBuilder();
            StringBuilder countSql = new StringBuilder();

            sql.Append(@"
                SELECT
                    op.Id,
                    op.OperationRecordId,
                    op.AllocateId,
                    op.OperationTime,
                    op.Operator,
                    op.OperationType,
                    op.OperationDescription,
                    op.PreviousState,
                    op.CurrentState,
                    op.Note,
                    a.OrderNumber
                FROM
                    operationrecordmodel op
                    JOIN allocatemodel a ON op.AllocateId = a.AllocateId
                WHERE
                    op.IsDel = 0 
                    AND a.IsDel = 0
            ");

            countSql.Append(@"
                SELECT COUNT(op.Id)
                FROM operationrecordmodel op
                    JOIN allocatemodel a ON op.AllocateId = a.AllocateId
                WHERE op.IsDel = 0 AND a.IsDel = 0
            ");

            // 添加筛选条件
            if (request.AllocateId.HasValue)
            {
                sql.Append(" AND op.AllocateId = @AllocateId");
                countSql.Append(" AND op.AllocateId = @AllocateId");
            }

            if (request.OperationTimeStart.HasValue)
            {
                sql.Append(" AND op.OperationTime >= @OperationTimeStart");
                countSql.Append(" AND op.OperationTime >= @OperationTimeStart");
            }

            if (request.OperationTimeEnd.HasValue)
            {
                sql.Append(" AND op.OperationTime <= @OperationTimeEnd");
                countSql.Append(" AND op.OperationTime <= @OperationTimeEnd");
            }

            if (!string.IsNullOrEmpty(request.Operator))
            {
                sql.Append(" AND op.Operator LIKE @Operator");
                countSql.Append(" AND op.Operator LIKE @Operator");
            }

            if (!string.IsNullOrEmpty(request.OperationType))
            {
                sql.Append(" AND op.OperationType LIKE @OperationType");
                countSql.Append(" AND op.OperationType LIKE @OperationType");
            }

            sql.Append(" ORDER BY op.OperationTime DESC LIMIT @PageSize OFFSET @offset");

            var parameters = new
            {
                AllocateId = request.AllocateId,
                OperationTimeStart = request.OperationTimeStart,
                OperationTimeEnd = request.OperationTimeEnd,
                Operator = $"%{request.Operator}%",
                OperationType = $"%{request.OperationType}%",
                PageSize = request.PageSize ?? 15,
                offset = ((request.PageIndex ?? 1) - 1) * (request.PageSize ?? 15)
            };

            List<OperationRecordDto> pageDatas;
            int totalCount;
            
            try
            {
                pageDatas = (await db.DapperConnection.QueryAsync<OperationRecordDto>(sql.ToString(), parameters)).ToList();
                totalCount = await db.DapperConnection.ExecuteScalarAsync<int>(countSql.ToString(), parameters);
            }
            catch (Exception ex) when (ex.Message.Contains("doesn't exist") || ex.Message.Contains("不存在"))
            {
                // 如果表不存在，返回空数据
                pageDatas = new List<OperationRecordDto>();
                totalCount = 0;
            }
            
            int totalPage = (int)Math.Ceiling(totalCount / (double)(request.PageSize ?? 15));

            result.Message = "查询成功";
            result.TotalPage = totalPage;
            result.TotalCount = totalCount;
            result.PageDatas = pageDatas.ToList();

            return result;
        }
    }
} 